package com.app.babygo.dbservice;

import java.text.ParseException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;

import com.app.babygo.exceptions.InternalDBException;
import com.app.babygo.exceptions.ObjectNotFoundException;
import com.app.babygo.obj.AchievementTask;
import com.app.babygo.obj.ChildAchievement;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.util.Log;

public class AchievementAdapter extends DBAdapter {
	
	// table info of the achievement
	protected static final String ID = "id";
	protected static final String ACHIEVEMENT_NAME = "name";
	//protected static final String ACHIEVEMENT_MIN_AGE = "min_age";
	//protected static final String ACHIEVEMENT_PREREQ = "pre_requirement";
	protected static final String ACHIEVEMENT_INFO = "info";
	
	protected static final String ACHIEVEMENT_ID = "achievement_id";
	protected static final String CHILD_ID = "child_id";
	protected static final String ACHIEVEMENT_DATETIME = "achievement_datetime";
	protected static final String ACHIEVEMENT_PIC_PATH = "achievement_pic_path";
	protected static String TAG = "AchievementAdapter";
	
	// table creation strings
	protected static final String DATABASE_CREATE_TABLE_ACHIEVMENT = "create table "
			+ DATABASE_TABLE_ACHIEVEMENT + " ("
			+ ID + " text primary key not null, "
			+ ACHIEVEMENT_NAME + " text not null, "
			+ ACHIEVEMENT_INFO + " text not null);";
	
	protected static final String DATABASE_CREATE_TABLE_CHILD_ACHIEVEMENT = "create table "
			+ DATABASE_TABLE_CHILD_ACHIEVEMENT + " ("
			+ ACHIEVEMENT_ID + " text not null, "
			+ CHILD_ID + " text not null, "
			+ ACHIEVEMENT_DATETIME + " text not null, "
			+ ACHIEVEMENT_PIC_PATH + " text, "
			+ "PRIMARY  KEY (" + ACHIEVEMENT_ID + " , "+ CHILD_ID + "), "
			+ "FOREIGN KEY (" + CHILD_ID + ") REFERENCES " + DATABASE_TABLE_CHILD + " (" + ID + ") ON DELETE CASCADE, "
			+ "FOREIGN KEY (" + ACHIEVEMENT_ID + ") REFERENCES " + DATABASE_TABLE_ACHIEVEMENT+" (" + ID + ") ON DELETE CASCADE);";

	public AchievementAdapter(Context context) {
		super(context);
	}
	
	public static boolean createTable(SQLiteDatabase db){
		try {
			db.execSQL(DATABASE_CREATE_TABLE_ACHIEVMENT);
		} catch (SQLException e) {
			Log.e(TAG, e.getMessage());
			return false;
		}
		Log.d(TAG, "Achievement Table created.");
		
		try {
			db.execSQL(DATABASE_CREATE_TABLE_CHILD_ACHIEVEMENT);
		} catch (SQLException e) {
			Log.e(TAG, e.getMessage());
			return false;
		}
		Log.d(TAG, "Child-Achievement Table created.");
		
		return initializeAchievements(db);
	}
	
	public static boolean initializeAchievements(SQLiteDatabase db) {
		// Insert pre-defined achievement here
		boolean result = true;
		result &= addAchievementTask(db, new AchievementTask(1, "First Word", "Say a word."));
		result &= addAchievementTask(db, new AchievementTask(2, "First Sentence", "Say a sentence."));
		result &= addAchievementTask(db, new AchievementTask(3, "First Walk", "Successful walking without anyone's help."));
		result &= addAchievementTask(db, new AchievementTask(4, "First Cry", "T_T"));
		result &= addAchievementTask(db, new AchievementTask(5, "First Blood", "Knock down another kid!"));
		return result;
	}
	
	public static boolean addAchievementTask(SQLiteDatabase db, AchievementTask task) {
		try{
			Log.d(TAG, task.toString());
			db.insert(DATABASE_TABLE_ACHIEVEMENT, null, getContentValues(task));
		} catch (SQLiteException e){
			Log.e(TAG, e.getMessage());
			return false; //TODO: throw exception?
		} catch (NullPointerException e) {
			Log.e(TAG, "Null Pointer!!");
			return false;
		}
		return true;
	}
	
	public boolean addAchievementTask(AchievementTask task) {
		try{
			Log.d(TAG, task.toString());
			db.insert(DATABASE_TABLE_ACHIEVEMENT, null, getContentValues(task));
		} catch (SQLiteException e){
			Log.e(TAG, e.getMessage());
			return false; //TODO: throw exception?
		} catch (NullPointerException e) {
			Log.e(TAG, "Null Pointer!!");
			return false;
		}
		return true;
	}
	
	public ArrayList<AchievementTask> getAchievementTaskList() throws InternalDBException {
		ArrayList<AchievementTask> result = new ArrayList<AchievementTask>();
		try{	
			String[] columns = { 
				ID,
				ACHIEVEMENT_NAME,
				ACHIEVEMENT_INFO,
			};
			
			Cursor cursor = db.query(DATABASE_TABLE_ACHIEVEMENT, columns, null, null, null, null, null);			
	
			if (cursor != null){
				cursor.moveToFirst();
			}	
			if (cursor.getCount() > 0) {
				do {
					AchievementTask task = new AchievementTask(); 
					task.setTaskid((getString(cursor, ID)));
					task.setTaskname((getString(cursor, ACHIEVEMENT_NAME)));
					task.setTaskdescription(getString(cursor, ACHIEVEMENT_INFO));
					result.add(task);
				} while(cursor.moveToNext());
			}
			
		} catch (SQLiteException e){
			Log.e(TAG, e.getMessage());
			throw new InternalDBException("Error getting list of tasks.");
			
		}
		
		return result;
	}
	
	public AchievementTask getAchievementTask(String taskid) throws InternalDBException, ObjectNotFoundException {
		try{	
			String[] columns = { 
				ID,
				ACHIEVEMENT_NAME,
				ACHIEVEMENT_INFO,
			};
			
			String[] SearchArg = { taskid };
			
			Cursor cursor = db.query(DATABASE_TABLE_ACHIEVEMENT, columns, ID + "=?", SearchArg, null, null, null);			
	
			if (cursor != null){
				cursor.moveToFirst();
			}	
			AchievementTask task = new AchievementTask(); 
			if (cursor.getCount() > 0) {
				task.setTaskid(taskid);
				task.setTaskname((getString(cursor, ACHIEVEMENT_NAME)));
				task.setTaskdescription(getString(cursor, ACHIEVEMENT_INFO));
			} else {
				Log.d(TAG, "Task not found. Task id: " + taskid);
				throw new ObjectNotFoundException("This task is not exist.");
			}
			return task;
			
		} catch (SQLiteException e){
			Log.e(TAG, e.getMessage());
			throw new InternalDBException("Error getting list of tasks.");
			
		}
	}
	
	public void updateAchievementTask(AchievementTask task) throws InternalDBException, ObjectNotFoundException {
		try{	
			String[] args = {task.getTaskid()};
			int rows = db.update(
				DATABASE_TABLE_ACHIEVEMENT, 
				getContentValues(task), 
				ID + "=?",
				args
			);
			
			if (rows < 1){
				Log.d(TAG, "Task data: " + task.toString());
				throw new ObjectNotFoundException("This task is not exist.");
			} else if (rows > 1){
				Log.w(TAG, "Updated more than one record.");
			}
			
		} catch (SQLiteException e){
			Log.e(TAG, e.getMessage());
			Log.d(TAG, "[Task id: " + task.getTaskid() + "] detail: "+ e.toString());
			throw new InternalDBException("Could not update task information.");
		}
	}
	
	public boolean deleteAchievementTask(String taskid) throws InternalDBException, ObjectNotFoundException {
		String[] args = {taskid};
		deleteChildAchievementByTaskId(taskid);
		int count = db.delete(DATABASE_TABLE_ACHIEVEMENT, ID + "=?", args);
		return count > 0;
	}
	
	public boolean unlockAchievement(String taskId, String childId, String imagePath) {
		return addChildAchievement(new ChildAchievement(taskId, childId, imagePath));
	}
	
	public boolean unlockAchievement(String taskId, String childId, Date unlockdate,String imagePath) {
		return addChildAchievement(new ChildAchievement(taskId, childId, unlockdate, imagePath));
	}
	
	public boolean addChildAchievement(ChildAchievement achievement) {
		try{
			Log.d(TAG, achievement.toString());
			db.insert(DATABASE_TABLE_CHILD_ACHIEVEMENT, null, getContentValues(achievement));
		} catch (SQLiteException e){
			Log.e(TAG, e.getMessage());
			return false; //TODO: throw exception?
		} catch (NullPointerException e) {
			Log.e(TAG, "Null Pointer!!");
			return false;
		}
		return true;
	}
	
	public ArrayList<ChildAchievement> getChildAchievementList(String childId) throws InternalDBException {
		ArrayList<ChildAchievement> result = new ArrayList<ChildAchievement>();
		try{	
			Cursor cursor = queryChildAchievement(childId);			
			if (cursor.getCount() > 0) {
				do {
					result.add(getChildAchievement(cursor));
				} while(cursor.moveToNext());
			}
			
		} catch (SQLiteException e){
			Log.e(TAG, e.getMessage());
			throw new InternalDBException("Error getting list of tasks.");
			
		} catch (ParseException e) {
			Log.e(TAG, e.getMessage());
			throw new InternalDBException("Unable to get achievement time.");
		}
		
		return result;
	}
	
	public ChildAchievement getChildAchievement(String childId, String taskId) throws InternalDBException {
		try{	
			Cursor cursor = queryChildAchievement(childId, taskId);			
			if (cursor.getCount() > 0) {
				return getChildAchievement(cursor);
			}
			
		} catch (SQLiteException e){
			Log.e(TAG, e.getMessage());
			throw new InternalDBException("Error getting child achievement.");
			
		} catch (ParseException e) {
			Log.e(TAG, e.getMessage());
			throw new InternalDBException("Unable to get achievement time.");
		}
		return null;
	}
	
	public HashMap<String, ChildAchievement> getChildAchievementMap(String childId) throws InternalDBException {
		HashMap<String, ChildAchievement> result = new HashMap<String, ChildAchievement>();
		try{	
			Cursor cursor = queryChildAchievement(childId);			
			if (cursor.getCount() > 0) {
				do {
					ChildAchievement task = getChildAchievement(cursor);
					result.put(task.getTaskid(), task);
				} while(cursor.moveToNext());
			}
			
		} catch (SQLiteException e){
			Log.e(TAG, e.getMessage());
			throw new InternalDBException("Error getting list of tasks.");
			
		} catch (ParseException e) {
			Log.e(TAG, e.getMessage());
			throw new InternalDBException("Unable to get achievement time.");
		}
		
		return result;
	}

	private ChildAchievement getChildAchievement(Cursor cursor) throws ParseException {
		ChildAchievement task = new ChildAchievement(); 
		task.setTaskid((getString(cursor, ACHIEVEMENT_ID)));
		task.setChildid((getString(cursor, CHILD_ID)));
		task.setAchievementDate(getDate(cursor, ACHIEVEMENT_DATETIME));
		task.setPicturePath(getString(cursor, ACHIEVEMENT_PIC_PATH));
		return task;
	}

	private Cursor queryChildAchievement(String childId) {
		String[] columns = { 
			ACHIEVEMENT_ID,
			CHILD_ID,
			ACHIEVEMENT_DATETIME,
			ACHIEVEMENT_PIC_PATH
		};
		String[] SearchArg = { childId };
		
		Cursor cursor = db.query(DATABASE_TABLE_CHILD_ACHIEVEMENT, columns,  CHILD_ID + "=?", SearchArg, null, null, null);			

		if (cursor != null){
			cursor.moveToFirst();
		}
		return cursor;
	}
	
	private Cursor queryChildAchievement(String childId, String taskId) {
		String[] columns = { 
			ACHIEVEMENT_ID,
			CHILD_ID,
			ACHIEVEMENT_DATETIME,
			ACHIEVEMENT_PIC_PATH
		};
		String[] SearchArg = { childId, taskId };
		
		Cursor cursor = db.query(DATABASE_TABLE_CHILD_ACHIEVEMENT, columns,  CHILD_ID + "=? and " + ACHIEVEMENT_ID + "=?", SearchArg, null, null, null);			

		if (cursor != null){
			cursor.moveToFirst();
		}
		return cursor;
	}
	
	public void updateChildAchievement(ChildAchievement achievement) throws InternalDBException, ObjectNotFoundException {
		try{	
			String[] args = {achievement.getTaskid(), achievement.getChildid()};
			int rows = db.update(
				DATABASE_TABLE_CHILD_ACHIEVEMENT, 
				getContentValues(achievement), 
				ACHIEVEMENT_ID + "=? and " + CHILD_ID + "=?",
				args
			);
			
			if (rows < 1){
				Log.d(TAG, "Task data: " + achievement.toString());
				throw new ObjectNotFoundException("This achievement is not exist.");
			} else if (rows > 1){
				Log.w(TAG, "Updated more than one record.");
			}
			
		} catch (SQLiteException e){
			Log.e(TAG, e.getMessage());
			Log.d(TAG, "[Achievement detail: " + achievement.toString() + "] detail: "+ e.toString());
			throw new InternalDBException("Could not update achievement information.");
		}
	}
	
	public boolean deleteChildAchievement(String taskid, String childid) {
		String[] args = {taskid, childid};
		int count = db.delete(DATABASE_TABLE_CHILD_ACHIEVEMENT, ACHIEVEMENT_ID + "=? and " + CHILD_ID + "=?", args);
		return count > 0;
	}
	
	protected boolean deleteChildAchievementByTaskId(String taskid) {
		String[] args = {taskid};
		int count = db.delete(DATABASE_TABLE_CHILD_ACHIEVEMENT, ACHIEVEMENT_ID + "=?", args);
		return count > 0;
	}
	
	public static boolean deleteChildAchievementByChildId(SQLiteDatabase db, String childid) {
		String[] args = {childid};
		int count = db.delete(DATABASE_TABLE_CHILD_ACHIEVEMENT, CHILD_ID + "=?", args);
		return count > 0;
	}
	
	protected static ContentValues getContentValues(AchievementTask task) {
		ContentValues contentValues = new ContentValues();
		contentValues.put(ID, task.getTaskid());
		contentValues.put(ACHIEVEMENT_NAME, task.getTaskname());
		contentValues.put(ACHIEVEMENT_INFO, task.getTaskdescription());
		return contentValues;
	}
	
	protected static ContentValues getContentValues(ChildAchievement achievement) {
		ContentValues contentValues = new ContentValues();
		contentValues.put(ACHIEVEMENT_ID, achievement.getTaskid());
		contentValues.put(CHILD_ID, achievement.getChildid());
		contentValues.put(ACHIEVEMENT_DATETIME, DB_DATE_FORMAT.format(achievement.getAchievementDate()));
		contentValues.put(ACHIEVEMENT_PIC_PATH, achievement.getPicturePath());
		return contentValues;
	}

}
